<?php if ( !defined('ABS_PATH') ) exit('ABS_PATH is not loaded. Direct access is not allowed.');

    /*
     *      Osclass – software for creating and publishing online classified
     *                           advertising platforms
     *
     *                        Copyright (C) 2012 OSCLASS
     *
     *       This program is free software: you can redistribute it and/or
     *     modify it under the terms of the GNU Affero General Public License
     *     as published by the Free Software Foundation, either version 3 of
     *            the License, or (at your option) any later version.
     *
     *     This program is distributed in the hope that it will be useful, but
     *         WITHOUT ANY WARRANTY; without even the implied warranty of
     *        MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
     *             GNU Affero General Public License for more details.
     *
     *      You should have received a copy of the GNU Affero General Public
     * License along with this program.  If not, see <http://www.gnu.org/licenses/>.
     */

    /**
     *
     */
    class Search extends DAO
    {
        /**
         *
         * @var type
         */
        private $conditions;
        private $itemConditions;
        private $tables;
        private $tables_join; // ?
        private $sql;
        private $order_column;
        private $order_direction;
        private $limit_init;
        private $results_per_page;
        private $cities;
        private $city_areas;
        private $regions;
        private $countries;
        private $categories;
        private $search_fields;
        private $total_results;
        private $total_results_table;
        private $sPattern;
        private $sEmail;
        private $groupBy;
        private $having;
        private $locale_code;

        private $withPattern;
        private $withPicture;
        private $withLocations;
        private $withCategoryId;
        private $withUserId;
        private $withItemId;
        private $withNoUserEmail;
        private $onlyPremium;

        private $price_min;
        private $price_max;

        private $user_ids;
        private $itemId;

        private static $instance;

        public static function newInstance()
        {
            if( !self::$instance instanceof self ) {
                self::$instance = new self;
            }
            return self::$instance;
        }

        /**
         *
         */
        function __construct($expired = false)
        {
            parent::__construct();
            $this->setTableName('t_item');
            $this->setFields( array('pk_i_id') );

            $this->withPattern      = false;
            $this->withLocations    = false;
            $this->withCategoryId   = false;
            $this->withUserId       = false;
            $this->withPicture      = false;
            $this->withNoUserEmail  = false;
            $this->onlyPremium      = false;

            $this->price_min = null;
            $this->price_max = null;

            $this->user_ids  = null;
            $this->itemId    = null;

            $this->city_areas       = array();
            $this->cities           = array();
            $this->regions          = array();
            $this->countries        = array();
            $this->categories       = array();
            $this->conditions       = array();
            $this->tables           = array();
            $this->tables_join      = array();
            $this->search_fields    = array();
            $this->itemConditions   = array();
            $this->locale_code      = array();
            $this->groupBy          = '';
            $this->having           = '';

            $this->order();
            $this->limit();
            $this->results_per_page = 10;

            if(!$expired) {
                // t_item
                $this->addItemConditions(sprintf("%st_item.b_enabled = 1 ", DB_TABLE_PREFIX));
                $this->addItemConditions(sprintf("%st_item.b_active = 1 ", DB_TABLE_PREFIX));
                $this->addItemConditions(sprintf("%st_item.b_spam = 0", DB_TABLE_PREFIX));
                $this->addItemConditions(sprintf("(%st_item.b_premium = 1 || %st_item.dt_expiration >= '%s')", DB_TABLE_PREFIX, DB_TABLE_PREFIX, date('Y-m-d H:i:s')) );
            }
            $this->total_results        = null;
            $this->total_results_table  = null;

            // get all item_location data
            if(OC_ADMIN) {
                $this->addField(sprintf('%st_item_location.*', DB_TABLE_PREFIX) );
            }

        }

        /**
         * Return an array with columns allowed for sorting
         *
         * @return array
         */
        public static function getAllowedColumnsForSorting()
        {
            return( array('i_price', 'dt_pub_date', 'dt_expiration') );
        }

        /**
         * Return an array with type of sorting
         *
         * @return array
         */
        public static function getAllowedTypesForSorting()
        {
            return ( array (0 => 'asc', 1 => 'desc') );
        }


        // juanramon: little hack to get alerts work in search layout
        public function reconnect()
        {
         //   $this->conn = getConnection();
        }

        /**
         * Add conditions to the search
         *
         * @access public
         * @since unknown
         * @param mixed $conditions
         */
        public function addConditions($conditions)
        {
            if(is_array($conditions)) {
                foreach($conditions as $condition) {
                    $condition = trim($condition);
                    if($condition!='') {
                        if(!in_array($condition, $this->conditions)) {
                            $this->conditions[] = $condition;
                        }
                    }
                }
            } else {
                $conditions = trim($conditions);
                if($conditions!='') {
                    if(!in_array($conditions, $this->conditions)) {
                        $this->conditions[] = $conditions;
                    }
                }
            }
        }

        /**
         * Add item conditions to the search
         *
         * @access public
         * @since unknown
         * @param mixed $conditions
         */
        public function addItemConditions($conditions)
        {
            if(is_array($conditions)) {
                foreach($conditions as $condition) {
                    $condition = trim($condition);
                    if($condition!='') {
                        if(!in_array($condition, $this->itemConditions)) {
                            $this->itemConditions[] = $condition;
                        }
                    }
                }
            } else {
                $conditions = trim($conditions);
                if($conditions!='') {
                    if(!in_array($conditions, $this->itemConditions)) {
                        $this->itemConditions[] = $conditions;
                    }
                }
            }
        }

        /**
         * Add locale conditions to the search
         *
         * @access public
         * @since 3.2
         * @param string $locale
         */
        public function addLocale($locale)
        {
            if(is_array($locale)) {
                foreach($locale as $l) {
                    if($l!='') {
                        $this->locale_code[$l] = $l;
                    }
                }
            } else {
                if($locale!='') {
                    $this->locale_code[$locale] = $locale;
                }
            }
        }

        /**
         * Add new fields to the search
         *
         * @access public
         * @since unknown
         * @param mixed $fields
         */
        public function addField($fields)
        {
            if(is_array($fields)) {
                foreach($fields as $field) {
                    $field = trim($field);
                    if($field!='') {
                        if(!in_array($field, $this->fields)) {
                            $this->search_fields[] = $field;
                        }
                    }
                }
            } else {
                $fields = trim($fields);
                if($fields!='') {
                    if(!in_array($fields, $this->fields)) {
                        $this->search_fields[] = $fields;
                    }
                }
            }
        }

        /**
         * Add extra table to the search
         *
         * @access public
         * @since unknown
         * @param mixed $tables
         */
        public function addTable($tables)
        {
            if(is_array($tables)) {
                foreach($tables as $table) {
                    $table = trim($table);
                    if($table!='') {
                        if(!in_array($table, $this->tables)) {
                            $this->tables[] = $table;
                        }
                    }
                }
            } else {
                $tables = trim($tables);
                if($tables!='') {
                    if(!in_array($tables, $this->tables)) {
                        $this->tables[] = $tables;
                    }
                }
            }
        }

        /**
         * Add group by to the search
         *
         * @access public
         * @since unknown
         * @param mixed $tables
         *
         */
        public function addGroupBy( $groupBy )
        {
            $this->groupBy = $groupBy;
        }

        /**
         * Establish the order of the search
         *
         * @access public
         * @since unknown
         * @param string $o_c column
         * @param string $o_d direction
         * @param string $table
         */
        public function order($o_c = 'dt_pub_date', $o_d = 'DESC', $table = NULL)
        {
            if($table == '') {
                $this->order_column = $o_c;
            } else if($table != ''){
                if( $table == '%st_user' ) {
                    $this->order_column = sprintf("ISNULL($table.$o_c), $table.$o_c", DB_TABLE_PREFIX, DB_TABLE_PREFIX);
                } else {
                    $this->order_column = sprintf("$table.$o_c", DB_TABLE_PREFIX);
                }
            }
            $this->order_direction = $o_d;
        }

        /**
         * Limit the results of the search
         *
         * @access public
         * @since unknown
         * @param int $l_i
         * @param int $t_p_p results per page
         */
        public function limit($l_i = 0, $r_p_p = null)
        {
            $this->limit_init = $l_i;
            if($r_p_p!=null) { $this->results_per_page = $r_p_p; };
        }

        /**
         * Limit the results of the search
         *
         * @access public
         * @since unknown
         * @param int $t_p_p results per page
         */
        public function set_rpp($r_p_p)
        {
            $this->results_per_page = $r_p_p;
        }

        /**
         * Select the page of the search
         *
         * @access public
         * @since unknown
         * @param int $p page
         * @param int $t_p_p results per page
         */
        public function page($p = 0, $r_p_p = null)
        {
            if($r_p_p!=null) { $this->results_per_page = $r_p_p; };
            $this->limit_init = $this->results_per_page*$p;
        }

        /**
         * Add city areas to the search
         *
         * @access public
         * @since unknown
         * @param mixed $city_area
         */
        public function addCityArea($city_area = array())
        {
            if(is_array($city_area)) {
                foreach($city_area as $c) {
                    $c = trim($c);
                    if($c!='') {
                        if(is_numeric($c)) {
                            $this->city_areas[] = sprintf("%st_item_location.fk_i_city_area_id = %d ", DB_TABLE_PREFIX, $this->dao->escapeStr($c));
                        } else {
                            $this->city_areas[] = sprintf("%st_item_location.s_city_area LIKE '%s' ", DB_TABLE_PREFIX, $this->dao->escapeStr($c));
                        }
                    }
                }
            } else {
                $city_area = trim($city_area);
                if($city_area!="") {
                    if(is_numeric($city_area)) {
                        $this->city_areas[] = sprintf("%st_item_location.fk_i_city_area_id = %d ", DB_TABLE_PREFIX, $this->dao->escapeStr($city_area));
                    } else {
                        $this->city_areas[] = sprintf("%st_item_location.s_city_area LIKE '%s' ", DB_TABLE_PREFIX, $this->dao->escapeStr($city_area));
                    }
                }
            }
        }

        /**
         * Add cities to the search
         *
         * @access public
         * @since unknown
         * @param mixed $city
         */
        public function addCity($city = array())
        {
            if(is_array($city)) {
                foreach($city as $c) {
                    $c = trim($c);
                    if($c!='') {
                        if(is_numeric($c)) {
                            $this->cities[] = sprintf("%st_item_location.fk_i_city_id = %d ", DB_TABLE_PREFIX, $this->dao->escapeStr($c));
                        } else {
                            $this->cities[] = sprintf("%st_item_location.s_city LIKE '%s' ", DB_TABLE_PREFIX, $this->dao->escapeStr($c));
                        }
                    }
                }
            } else {
                $city = trim($city);
                if($city!="") {
                    if(is_numeric($city)) {
                        $this->cities[] = sprintf("%st_item_location.fk_i_city_id = %d ", DB_TABLE_PREFIX, $this->dao->escapeStr($city));
                    } else {
                        $this->cities[] = sprintf("%st_item_location.s_city LIKE '%s' ", DB_TABLE_PREFIX, $this->dao->escapeStr($city));
                    }
                }
            }
        }

        /**
         * Add regions to the search
         *
         * @access public
         * @since unknown
         * @param mixed $region
         */
        public function addRegion($region = array())
        {
            if(is_array($region)) {
                foreach($region as $r) {
                    $r = trim($r);
                    if($r!='') {
                        if(is_numeric($r)) {
                            $this->regions[] = sprintf("%st_item_location.fk_i_region_id = %d ", DB_TABLE_PREFIX, $this->dao->escapeStr($r));
                        } else {
                            $this->regions[] = sprintf("%st_item_location.s_region LIKE '%s' ", DB_TABLE_PREFIX, $this->dao->escapeStr($r));
                        }
                    }
                }
            } else {
                $region = trim($region);
                if($region!="") {
                    if(is_numeric($region)) {
                        $this->regions[] = sprintf("%st_item_location.fk_i_region_id = %d ", DB_TABLE_PREFIX, $this->dao->escapeStr($region));
                    } else {
                        $this->regions[] = sprintf("%st_item_location.s_region LIKE '%s' ", DB_TABLE_PREFIX, $this->dao->escapeStr($region));
                    }
                }
            }
        }

        /**
         * Add countries to the search
         *
         * @access public
         * @since unknown
         * @param mixed $country
         */
        public function addCountry($country = array())
        {
            if(is_array($country)) {
                foreach($country as $c) {
                    $c = trim($c);
                    if($c!='') {
                        if(strlen($c)==2) {
                            $this->countries[] = sprintf("%st_item_location.fk_c_country_code = '%s' ", DB_TABLE_PREFIX, strtolower($this->dao->escapeStr($c)));
                        } else {
                            $this->countries[] = sprintf("%st_item_location.s_country LIKE '%s' ", DB_TABLE_PREFIX, $this->dao->escapeStr($c));
                        }
                    }
                }
            } else {
                $country = trim($country);
                if($country!="") {
                    if(strlen($country)==2) {
                        $this->countries[] = sprintf("%st_item_location.fk_c_country_code = '%s' ", DB_TABLE_PREFIX, strtolower($this->dao->escapeStr($country)));
                    } else {
                        $this->countries[] = sprintf("%st_item_location.s_country LIKE '%s' ", DB_TABLE_PREFIX, $this->dao->escapeStr($country));
                    }
                }
            }
        }

        /**
         * Establish price range
         *
         * @access public
         * @since unknown
         * @param int $price_min
         * @param int $price_max
         */
        public function priceRange( $price_min = 0, $price_max = 0)
        {
            $this->price_min = 1000000*$price_min;
            $this->price_max = 1000000*$price_max;
        }

        private function _priceRange()
        {
            if(is_numeric($this->price_min) && $this->price_min!=0) {
                $this->dao->where(sprintf("i_price >= %0.0f", $this->price_min));
            }
            if(is_numeric($this->price_max) && $this->price_max>0) {
                $this->dao->where(sprintf("i_price <= %0.0f", $this->price_max));
            }
        }

        /**
         * Establish max price
         *
         * @access public
         * @since unknown
         * @param int $price
         */
        public function priceMax($price)
        {
            $this->priceRange(null, $price);
        }

        /**
         * Establish min price
         *
         * @access public
         * @since unknown
         * @param int $price
         */
        public function priceMin($price)
        {
            $this->priceRange($price, null);
        }

        /**
         * Set having sentence to sql
         *
         * @param type $having
         */
        public function addHaving($having)
        {
            $this->having = $having;
        }

        /**
         * Filter by ad with picture or not
         *
         * @access public
         * @since unknown
         * @param bool $pic
         */
        public function withPicture($pic = false)
        {
            $this->withPicture = $pic;
        }

        /**
         * Filter by premium ad status
         *
         * @access public
         * @since 3.2
         * @param bool $premium
         */
        public function onlyPremium($premium = false)
        {
            $this->onlyPremium = $premium;
        }

        /**
         * Filter by search pattern
         *
         * @access public
         * @since 2.4
         * @param string $pattern
         */
        public function addPattern($pattern)
        {
            $this->withPattern  = true;
            $this->sPattern     = $this->dao->escapeStr($pattern);
        }

        /**
         * Filter by email
         *
         * @access public
         * @since 2.4
         * @param string $pattern
         */
        public function addContactEmail($email)
        {
            $this->withNoUserEmail  = true;
            $this->sEmail = $email;
        }

        /**
         * Return ads from specified users
         *
         * @access public
         * @since unknown
         * @param mixed $id
         */
        public function fromUser($id = NULL)
        {
            if(is_array($id)) {
                $this->withUserId = true;
                $ids = array();
                foreach($id as $_id) {
                    if(!is_numeric($_id)) {
                        $user = User::newInstance()->findByUsername($_id);
                        if(isset($user['pk_i_id'])) {
                            $ids[] = sprintf("%st_item.fk_i_user_id = %d ", DB_TABLE_PREFIX, $this->dao->escapeStr($user['pk_i_id']));
                        }
                    } else {
                        $ids[] = sprintf("%st_item.fk_i_user_id = %d ", DB_TABLE_PREFIX, $_id);
                    }
                }
                $this->user_ids = $ids;
            } else {
                $this->withUserId = true;
                if(!is_numeric($id)) {
                    $user = User::newInstance()->findByUsername($id);
                    if(isset($user['pk_i_id'])) {
                        $this->user_ids = $this->dao->escapeStr($user['pk_i_id']);
                    }
                } else {
                    $this->user_ids = $this->dao->escapeStr($id);
                }
            }
        }

        private function _fromUser()
        {
            $this->dao->from(sprintf('%st_user',DB_TABLE_PREFIX));
            $this->dao->where(sprintf('%st_user.pk_i_id = %st_item.fk_i_user_id',DB_TABLE_PREFIX,DB_TABLE_PREFIX));

            if(is_array($this->user_ids)) {
                $this->dao->where(" ( ".implode(" || ", $this->user_ids)." ) ");
            } else {
                $this->dao->where(sprintf("%st_item.fk_i_user_id = %d ", DB_TABLE_PREFIX, $this->user_ids));
            }
        }

        public function addItemId($id)
        {
            $this->withItemId = true;
            $this->itemId = $id;
        }

        /**
         * Clear the categories
         *
         * @access private
         * @since unknown
         * @param array $branches
         */
        private function pruneBranches($branches = null)
        {
            if($branches!=null) {
                foreach($branches as $branch) {
                    if(!in_array($branch['pk_i_id'], $this->categories)) {
                        $this->categories[] = $branch['pk_i_id'];
                        if(isset($branch['categories'])) {
                            $this->pruneBranches($branch['categories']);
                        }
                    }
                }
            }
        }

        /**
         * Add categories to the search
         *
         * @access public
         * @since unknown
         * @param mixed $category
         */
        public function addCategory($category = null)
        {
            if( $category == null ) {
                return false;
            }

            if( !is_numeric($category) ) {
                $category  = preg_replace('|/$|','',$category);
                $aCategory = explode('/', $category);
                $category  = Category::newInstance()->findBySlug($aCategory[count($aCategory)-1]);

                if( count($category) == 0 ) {
                    return false;
                }

                $category  = $category['pk_i_id'];
            }

            $tree = Category::newInstance()->toSubTree($category);
            if( !in_array($category, $this->categories) ) {
                $this->categories[] = $category;
            }
            $this->pruneBranches($tree);
            return true;
        }

        /**
         *  Add joins for future use
         *
         * @since 2.4
         * @param string $key
         * @param string $table
         * @param string $condition
         * @param string $type
         */
        public function addJoinTable($key, $table, $condition, $type)
        {
            $this->tables_join[$key] = array($table, $condition, $type);
        }

        /**
         * Add join to current query
         *
         * @since 2.4
         */
        private function _joinTable()
        {
            foreach($this->tables_join as $tJoin) {
                $this->dao->join( $tJoin[0], $tJoin[1], $tJoin[2] );
            }
        }

        /**
         * Create extraFields & conditionsSQL and return as an array
         *
         * @return array with extraFields & conditions strings
         */
        private function _conditions()
        {
            if(count($this->city_areas)>0) {
                $this->withLocations = true;
            }

            if(count($this->cities)>0) {
                $this->withLocations = true;
            }

            if(count($this->regions)>0) {
                $this->withLocations = true;
            }

            if(count($this->countries)>0) {
                $this->withLocations = true;
            }

            if(count($this->categories)>0) {
                $this->withCategoryId = true;
            }

            $conditionsSQL = implode(' AND ', osc_apply_filter('sql_search_conditions', $this->conditions));
            if($conditionsSQL!='') {
                $conditionsSQL = " ".$conditionsSQL;
            }

            $extraFields = "";
            if( count($this->search_fields) > 0 ) {
                $extraFields = ",";
                $extraFields .= implode(' ,', osc_apply_filter('sql_search_fields', $this->search_fields));
            }

            return array(
                'extraFields'    => $extraFields,
                'conditionsSQL'  => $conditionsSQL
            );
        }

        /**
         * Only search by pattern + location + category
         *
         * @param type $num
         */
        private function _makeSQLPremium($num = 2)
        {
            if ($this->withPattern ) {
                // sub select for JOIN ----------------------
                $this->dao->select('distinct d.fk_i_item_id');
                $this->dao->from(DB_TABLE_PREFIX . 't_item_description as d');
                $this->dao->where(sprintf("MATCH(d.s_title, d.s_description) AGAINST('%s' IN BOOLEAN MODE)", $this->sPattern));

                if(empty($this->locale_code)) {
                    if(OC_ADMIN) {
                        $this->locale_code[osc_current_admin_locale()] = osc_current_admin_locale();
                    } else {
                        $this->locale_code[osc_current_user_locale()] = osc_current_user_locale();
                    }
                }
                $this->dao->where(sprintf("( d.fk_c_locale_code LIKE '%s' )", implode("' d.fk_c_locale_code LIKE '", $this->locale_code)));

                $subSelect = $this->dao->_getSelect();
                $this->dao->_resetSelect();
                // END sub select ----------------------
                $this->dao->select(DB_TABLE_PREFIX.'t_item.*, '.DB_TABLE_PREFIX.'t_item.s_contact_name as s_user_name');
                $this->dao->from( DB_TABLE_PREFIX.'t_item' );
                $this->dao->from(sprintf('%st_item_stats', DB_TABLE_PREFIX));
                $this->dao->where(sprintf('%st_item_stats.fk_i_item_id = %st_item.pk_i_id', DB_TABLE_PREFIX, DB_TABLE_PREFIX));
                $this->dao->where(sprintf("%st_item.b_premium = 1", DB_TABLE_PREFIX));

                if($this->withLocations || OC_ADMIN) {
                    $this->dao->join(sprintf('%st_item_location', DB_TABLE_PREFIX), sprintf('%st_item_location.fk_i_item_id = %st_item.pk_i_id', DB_TABLE_PREFIX, DB_TABLE_PREFIX), 'LEFT');
                    $this->_addLocations();
                }
                if($this->withCategoryId && (count($this->categories) > 0)) {
                    $this->dao->where(sprintf("%st_item.fk_i_category_id", DB_TABLE_PREFIX) .' IN ('. implode(', ', $this->categories) .')' );
                }
                $this->dao->where(DB_TABLE_PREFIX.'t_item.pk_i_id IN ('.$subSelect.')');

                $this->dao->groupBy(DB_TABLE_PREFIX.'t_item.pk_i_id');
                $this->dao->orderBy(sprintf('%st_item_stats.i_num_premium_views', DB_TABLE_PREFIX), 'ASC');
                $this->dao->orderBy(null, 'random');
                $this->dao->limit(0, $num);
            } else {
                $this->dao->select(DB_TABLE_PREFIX.'t_item.*, '.DB_TABLE_PREFIX.'t_item.s_contact_name as s_user_name');
                $this->dao->from( DB_TABLE_PREFIX.'t_item' );
                $this->dao->from(sprintf('%st_item_stats', DB_TABLE_PREFIX));
                $this->dao->where(sprintf('%st_item_stats.fk_i_item_id = %st_item.pk_i_id', DB_TABLE_PREFIX, DB_TABLE_PREFIX));
                $this->dao->where(sprintf("%st_item.b_premium = 1", DB_TABLE_PREFIX));

                if($this->withLocations || OC_ADMIN) {
                    $this->dao->join(sprintf('%st_item_location', DB_TABLE_PREFIX), sprintf('%st_item_location.fk_i_item_id = %st_item.pk_i_id', DB_TABLE_PREFIX, DB_TABLE_PREFIX), 'LEFT');
                    $this->_addLocations();
                }
                if( $this->withCategoryId && (count($this->categories) > 0) ) {
                    $this->dao->where(sprintf("%st_item.fk_i_category_id", DB_TABLE_PREFIX) .' IN ('. implode(', ', $this->categories) .')' );
                }

                $this->dao->groupBy(DB_TABLE_PREFIX.'t_item.pk_i_id');
                $this->dao->orderBy(sprintf('%st_item_stats.i_num_premium_views', DB_TABLE_PREFIX), 'ASC');
                $this->dao->orderBy(null, 'random');
                $this->dao->limit(0, $num);
            }

            $sql = $this->dao->_getSelect();
            // reset dao attributes
            $this->dao->_resetSelect();

            return $sql;
        }

        private function _addLocations()
        {
            if(count($this->city_areas)>0) {
                $this->dao->where("( ".implode(' || ', $this->city_areas)." )");
            }
            if(count($this->cities)>0) {
                $this->dao->where("( ".implode(' || ', $this->cities)." )");
            }
            if(count($this->regions)>0) {
                $this->dao->where("( ".implode(' || ', $this->regions)." )");
            }
            if(count($this->countries)>0) {
                $this->dao->where("( ".implode(' || ', $this->countries)." )");
            }
        }

        /**
         * Make the SQL for the search with all the conditions and filters specified
         *
         * @access private
         * @since unknown
         * @param bool $count
         */
        private function _makeSQL($count = false,$premium = false)
        {
            $arrayConditions    = $this->_conditions();
            $extraFields        = $arrayConditions['extraFields'];
            $conditionsSQL      = $arrayConditions['conditionsSQL'];

            $sql = '';

            if($this->withItemId) {
                // add field s_user_name
                $this->dao->select(sprintf('%st_item.*, %st_item.s_contact_name as s_user_name', DB_TABLE_PREFIX, DB_TABLE_PREFIX) );
                $this->dao->from(sprintf('%st_item', DB_TABLE_PREFIX));
                $this->dao->where('pk_i_id', (int)$this->itemId);
            } else {
                if($count) {
                    $this->dao->select(DB_TABLE_PREFIX.'t_item.pk_i_id');
                    $this->dao->select($extraFields); // plugins!
                } else {
                    $this->dao->select(DB_TABLE_PREFIX.'t_item.*, '.DB_TABLE_PREFIX.'t_item.s_contact_name as s_user_name');
                    $this->dao->select($extraFields); // plugins!
                }
                $this->dao->from(DB_TABLE_PREFIX.'t_item');

                if($this->withNoUserEmail) {
                    $this->dao->where( DB_TABLE_PREFIX.'t_item.s_contact_email', $this->sEmail );
                }

                if ($this->withPattern ) {
                    $this->dao->join(DB_TABLE_PREFIX.'t_item_description as d','d.fk_i_item_id = '.DB_TABLE_PREFIX.'t_item.pk_i_id','LEFT');
                    $this->dao->where(sprintf("MATCH(d.s_title, d.s_description) AGAINST('%s' IN BOOLEAN MODE)", $this->sPattern) );
                    if(empty($this->locale_code)) {
                        if(OC_ADMIN) {
                            $this->locale_code[osc_current_admin_locale()] = osc_current_admin_locale();
                        } else {
                            $this->locale_code[osc_current_user_locale()] = osc_current_user_locale();
                        }
                    }
                    $this->dao->where(sprintf("( d.fk_c_locale_code LIKE '%s' )", implode("' d.fk_c_locale_code LIKE '", $this->locale_code)));
                }

                // item conditions
                if(count($this->itemConditions)>0) {
                    $itemConditions = implode(' AND ', osc_apply_filter('sql_search_item_conditions', $this->itemConditions));
                    $this->dao->where($itemConditions);
                }
                if( $this->withCategoryId && (count($this->categories) > 0) ) {
                    $this->dao->where(sprintf("%st_item.fk_i_category_id", DB_TABLE_PREFIX) .' IN ('. implode(', ', $this->categories) .')' );
                }
                if($this->withUserId) {
                    $this->_fromUser();
                }
                if($this->withLocations || OC_ADMIN) {
                    $this->dao->join(sprintf('%st_item_location', DB_TABLE_PREFIX), sprintf('%st_item_location.fk_i_item_id = %st_item.pk_i_id', DB_TABLE_PREFIX, DB_TABLE_PREFIX), 'LEFT');
                    $this->_addLocations();
                }
                if($this->withPicture) {
                    $this->dao->join(sprintf('%st_item_resource', DB_TABLE_PREFIX), sprintf('%st_item_resource.fk_i_item_id = %st_item.pk_i_id', DB_TABLE_PREFIX, DB_TABLE_PREFIX), 'LEFT');
                    $this->dao->where(sprintf("%st_item_resource.s_content_type LIKE '%%image%%' ", DB_TABLE_PREFIX, DB_TABLE_PREFIX, DB_TABLE_PREFIX));
                    $this->dao->groupBy(DB_TABLE_PREFIX.'t_item.pk_i_id');
                }
                if($this->onlyPremium) {
                    $this->dao->where(sprintf("%st_item.b_premium = 1", DB_TABLE_PREFIX));
                }
                $this->_priceRange();

                // add joinTables
                $this->_joinTable();

                // PLUGINS TABLES !!
                if( !empty($this->tables) ) {
                    $tables = implode(', ', $this->tables);
                    $this->dao->from($tables);
                }
                // WHERE PLUGINS extra conditions
                if(count($this->conditions) > 0) {
                    $this->dao->where($conditionsSQL);
                }
                // ---------------------------------------------------------
                // groupBy
                if($this->groupBy != '') {
                    $this->dao->groupBy( $this->groupBy );
                }
                // having
                if($this->having != '') {
                    $this->dao->having($this->having);
                }
                // ---------------------------------------------------------

                // order & limit
                $this->dao->orderBy( $this->order_column, $this->order_direction);

                if($count) {
                    $this->dao->limit(100*$this->results_per_page);
                } else {
                    $this->dao->limit( $this->limit_init, $this->results_per_page);
                }
            }

            $this->sql = $this->dao->_getSelect();
            // reset dao attributes
            $this->dao->_resetSelect();

            return $this->sql;
        }

        /**
         * Return number of ads selected
         *
         * @access public
         * @since unknown
         */
        public function count()
        {
            if( is_null($this->total_results) ) {
                $this->doSearch();
            }
            return $this->total_results;
        }

        /**
         * Return total items on t_item without any filter
         *
         * @return type
         */
        public function countAll()
        {
            if( is_null($this->total_results_table) ) {
                $result = $this->dao->query(sprintf('select count(*) as total from %st_item', DB_TABLE_PREFIX ));
                $row = $result->row();
                $this->total_results_table = $row['total'];
            }
            return $this->total_results_table;
        }

        /**
         * Perform the search
         *
         * @access public
         * @since unknown
         * @param bool $extended if you want to extend ad's data
         */
        public function doSearch($extended = true, $count = true)
        {
            $sql = $this->_makeSQL(false);
            $result = $this->dao->query($sql);
            if($count) {
                $sql = $this->_makeSQL(true);
                $datatmp  = $this->dao->query( $sql );

                if( $datatmp == false ) {
                    $this->total_results = 0;
                } else {
                    $this->total_results = $datatmp->numRows();
                }
            } else {
                $this->total_results = 0;
            }

            if( $result == false ) {
                return array();
            }

            if($result) {
                $items = $result->result();
            } else {
                $items = array();
            }

            if($extended) {
                return Item::newInstance()->extendData($items);
            } else {
                return $items;
            }
        }

        /**
         * Return premium ads related to the search
         *
         * @access public
         * @since unknown
         * @param int $max
         */
        /**
         * solo acepta pattern + location + stats, category
         *
         */
        public function getPremiums($max = 2)
        {
            $premium_sql = $this->_makeSQLPremium($max); // make premium sql

            $result = $this->dao->query($premium_sql);
            if($result) {
                $items = $result->result();

                $mStat = ItemStats::newInstance();
                foreach($items as $item) {
                    $mStat->increase('i_num_premium_views', $item['pk_i_id']);
                }
                return Item::newInstance()->extendData($items);
            } else {
                return array();
            }
        }


        /**
         * Return latest posted items, you can filter by category and specify the
         * number of items returned.
         *
         * @param int $numItems
         * @param mixed $options
         * @param bool $withPicture
         * @return array
         */
        public function getLatestItems($numItems = 10, $options = array(), $withPicture = false)
        {
            $this->set_rpp($numItems);
            if($withPicture) {
                $this->withPicture(true);
            }
            if(isset($options['sCategory'])) {
                $this->addCategory($options['sCategory']);
            }
            if(isset($options['sCountry'])) {
                $this->addCountry($options['sCountry']);
            }
            if(isset($options['sRegion'])) {
                $this->addRegion($options['sRegion']);
            }
            if(isset($options['sCity'])) {
                $this->addCity($options['sCity']);
            }
            if(isset($options['sUser'])) {
                $this->fromUser($options['sUser']);
            }
            return $this->doSearch();
        }

        /**
         * Returns number of ads from each country
         *
         * @deprecated
         * @access public
         * @since unknown
         * @param string $zero if you want to include locations with zero results
         * @param string $order
         */
        public function listCountries($zero = ">", $order = "items DESC")
        {
           return CountryStats::newInstance()->listCountries($zero, $order);
        }

        /**
         * Returns number of ads from each region
         * <code>
         *  Search::newInstance()->listRegions($country, ">=", "country_name ASC" )
         * </code>
         *
         * @deprecated
         * @access public
         * @since unknown
         * @param string $country
         * @param string $zero if you want to include locations with zero results
         * @param string $order
         */
        public function listRegions($country = '%%%%', $zero = ">", $order = "items DESC")
        {
           return RegionStats::newInstance()->listRegions($country, $zero, $order);
        }

        /**
         * Returns number of ads from each city
         *
         * <code>
         *  Search::newInstance()->listCities($region, ">=", "city_name ASC" )
         * </code>
         *
         * @deprecated
         * @access public
         * @since unknown
         * @param string $region
         * @param string $zero if you want to include locations with zero results
         * @param string $order
         */
        public function listCities($region = null, $zero = ">", $order = "city_name ASC")
        {
            return CityStats::newInstance()->listCities($region, $zero, $order);
        }

        /**
         * Returns number of ads from each city area
         *
         * @access public
         * @since unknown
         * @param string $city
         * @param string $zero if you want to include locations with zero results
         * @param string $order
         */
        public function listCityAreas($city = null, $zero = ">", $order = "items DESC")
        {
           $aOrder = explode(' ', $order);
            $nOrder = count($aOrder);

            if($nOrder == 2) $this->dao->orderBy($aOrder[0], $aOrder[1]);
            else if($nOrder == 1) $this->dao->orderBy($aOrder[0], 'DESC');
            else $this->dao->orderBy('item', 'DESC');

            $this->dao->select('fk_i_city_area_id as city_area_id, s_city_area as city_area_name, fk_i_city_id , s_city as city_name, fk_i_region_id as region_id, s_region as region_name, fk_c_country_code as pk_c_code, s_country as country_name, count(*) as items');
            $this->dao->from(DB_TABLE_PREFIX.'t_item, '.DB_TABLE_PREFIX.'t_item_location, '.DB_TABLE_PREFIX.'t_category, '.DB_TABLE_PREFIX.'t_country');
            $this->dao->where(DB_TABLE_PREFIX.'t_item.pk_i_id = '.DB_TABLE_PREFIX.'t_item_location.fk_i_item_id');
            $this->dao->where(DB_TABLE_PREFIX.'t_item.b_enabled = 1');
            $this->dao->where(DB_TABLE_PREFIX.'t_item.b_active = 1');
            $this->dao->where(DB_TABLE_PREFIX.'t_item.b_spam = 0');
            $this->dao->where(DB_TABLE_PREFIX.'t_category.b_enabled = 1');
            $this->dao->where(DB_TABLE_PREFIX.'t_category.pk_i_id = '.DB_TABLE_PREFIX.'t_item.fk_i_category_id');
            $this->dao->where('('.DB_TABLE_PREFIX.'t_item.b_premium = 1 || '.DB_TABLE_PREFIX.'t_category.i_expiration_days = 0 || DATEDIFF(\''.date('Y-m-d H:i:s').'\','.DB_TABLE_PREFIX.'t_item.dt_pub_date) < '.DB_TABLE_PREFIX.'t_category.i_expiration_days)');
            $this->dao->where('fk_i_city_area_id IS NOT NULL');
            $this->dao->where(DB_TABLE_PREFIX.'t_country.pk_c_code = fk_c_country_code');
            $this->dao->groupBy('fk_i_city_area_id');
            $this->dao->having("items $zero 0");

            $city_int = (int)$city;

            if(is_numeric($city_int) && $city_int!=0) {
                $this->dao->where("fk_i_city_id = $city_int");
            }

            $result = $this->dao->get();
            if($result) {
                return $result->result();
            } else {
                return array();
            }
        }

        /**
         * Given the current search object, extract search parameters & conditions
         * as array.
         *
         * @return array
         */
        private function _getConditions()
        {
            $aData = array();

            $item_id                = DB_TABLE_PREFIX.'t_item.pk_i_id';
            $item_category_id       = DB_TABLE_PREFIX.'t_item.fk_i_category_id';
            $item_description_id    = 'd.fk_i_item_id';
            $category_id            = DB_TABLE_PREFIX.'t_category.pk_i_id';
            $item_location_id       = DB_TABLE_PREFIX.'t_item_location.fk_i_item_id';
            $item_resource_id       = DB_TABLE_PREFIX.'t_item_resource.fk_i_item_id';

            // get item conditions
            foreach($this->conditions as $condition) {
                // item table
                if(preg_match('/'.DB_TABLE_PREFIX.'t_item\.b_active/', $condition, $matches) ) {
                    $aData['itemConditions'][] = $condition;
                } else if(preg_match('/'.DB_TABLE_PREFIX.'t_item\.b_spam/', $condition, $matches) ) {
                    $aData['itemConditions'][] = $condition;
                } else if(preg_match('/'.DB_TABLE_PREFIX.'t_item\.b_enabled/', $condition, $matches) ) {
                    $aData['itemConditions'][] = $condition;
                } else if(preg_match('/'.DB_TABLE_PREFIX.'t_item\.b_premium/', $condition, $matches) ) {
                    $aData['itemConditions'][] = $condition;
                } else if(preg_match('/('.DB_TABLE_PREFIX.'t_item\.)?f_price >= (.*)/', $condition, $matches) ) {
                    $aData['price_min'] = (int) $matches[2];
                } else if(preg_match('/('.DB_TABLE_PREFIX.'t_item\.)?f_price <= (.*)/', $condition, $matches) ) {
                    $aData['price_max'] = (int) $matches[2];
                } else if(preg_match('/('.DB_TABLE_PREFIX.'t_item\.)?i_price >= (.*)/', $condition, $matches) ) {
                    $aData['price_min'] = ( (double) $matches[2] / 1000000 );
                } else if(preg_match('/('.DB_TABLE_PREFIX.'t_item\.)?i_price <= (.*)/', $condition, $matches) ) {
                    $aData['price_max'] = ( (double) $matches[2] / 1000000 );
                } else if(preg_match('/'.DB_TABLE_PREFIX.'t_category.b_enabled/', $condition, $matches) ) {
                    // t_category.b_enabled is not longer needed
                } else if(preg_match_all('/('.DB_TABLE_PREFIX.'t_item_location.s_city_area\s*LIKE\s*\'%([\s\p{L}\p{N}]*)%\'\s*)/u', $condition, $matches) ) { // OJO
                    // Comprobar: si ( s_name existe ) then get location id,
                    $aData['s_city_area'][] = DB_TABLE_PREFIX.'t_item_location.s_city_area LIKE \'%'.$matches[2][0].'%\'';
                } else if(preg_match('/'.DB_TABLE_PREFIX.'t_item_location.fk_i_city_area_id = (.*)/', $condition, $matches) ) {
                    $aData['fk_i_city_area_id'][] = DB_TABLE_PREFIX.'t_item_location.fk_i_city_area_id = '.$matches[1];
                } else if(preg_match_all('/('.DB_TABLE_PREFIX.'t_item_location.s_city\s*LIKE\s*\'%([\s\p{L}\p{N}]*)%\'\s*)/u', $condition, $matches) ) { // OJO
                    // Comprobar: si ( s_name existe ) then get location id,
                    $aData['cities'][] = DB_TABLE_PREFIX.'t_item_location.s_city LIKE \'%'.$matches[2][0].'%\'';
                } else if(preg_match('/'.DB_TABLE_PREFIX.'t_item_location.fk_i_city_id = (.*)/', $condition, $matches) ) {
                    $aData['cities'][] = DB_TABLE_PREFIX.'t_item_location.fk_i_city_id = '.$matches[1];
                } else if(preg_match_all('/('.DB_TABLE_PREFIX.'t_item_location.s_region\s*LIKE\s*\'%([\s\p{L}\p{N}]*)%\'\s*)/u', $condition, $matches) ) { // OJO
                    // Comprobar: si ( s_name existe ) then get location id,
                    $aData['s_region'][] = DB_TABLE_PREFIX.'t_item_location.s_region LIKE \'%'.$matches[2][0].'%\'';
                } else if(preg_match('/'.DB_TABLE_PREFIX.'t_item_location.fk_i_region_id = (.*)/', $condition, $matches) ) {
                    $aData['fk_i_region_id'] = DB_TABLE_PREFIX.'t_item_location.fk_i_region_id = '.$matches[1];
                } else if(preg_match_all('/('.DB_TABLE_PREFIX.'t_item_location.s_country\s*LIKE\s*\'%([\s\p{L}\p{N}]*)%\'\s*)/u', $condition, $matches) ) { // OJO
                    // Comprobar: si ( s_name existe ) then get location id,
                    $aData['s_country'][] = DB_TABLE_PREFIX.'t_item_location.s_country LIKE \'%'.$matches[2][0].'%\'';
                } else if(preg_match('/'.DB_TABLE_PREFIX.'t_item_location.fk_c_country_code = \'?(.*)\'?/', $condition, $matches) ) {
                    $aData['fk_c_country_code'][] = DB_TABLE_PREFIX.'t_item_location.fk_c_country_code = '.$matches[1];
                } else if(preg_match('/d\.s_title\s*LIKE\s*\'%([\s\p{L}\p{N}]*)%\'/u', $condition, $matches) ) {  // OJO
                    $aData['sPattern']      = $matches[1];
                    $aData['withPattern']   = true;
                } else if(preg_match('/MATCH\(d\.s_title, d\.s_description\) AGAINST\(\'([\s\p{L}\p{N}]*)\' IN BOOLEAN MODE\)/u', $condition, $matches) ) { // OJO
                    $aData['sPattern'] = $matches[1];
                    $aData['withPattern']   = true;
                } else if(preg_match("/$item_id\s*=\s*$item_description_id/", $condition, $matches_1)   || preg_match("/$item_description_id\s*=\s*$item_id/", $condition, $matches_2)) {
                } else if(preg_match("/$category_id\s*=\s*$item_category_id/", $condition, $matches_1)  || preg_match("/$item_id\s*=\s*$item_category_id/", $condition, $matches_2)) {
                } else if(preg_match("/$item_location_id\s*=\s*$item_id/", $condition, $matches_1)      || preg_match("/$item_id\s*=\s*$item_location_id/", $condition, $matches_2)) {
                } else if(preg_match("/$item_id\s*=\s*$item_resource_id/", $condition, $matches_1)      || preg_match("/$item_resource_id\s*=\s*$item_id/", $condition, $matches_2)) {
                    // nothing to do, catch table
                } else if(preg_match_all('/('.DB_TABLE_PREFIX.'t_item\.fk_i_category_id = (\d*))/', $condition, $matches) ) {
                    $aData['aCategories'] = $matches[2];
                } else {
                    $aData['no_catched_conditions'][] = $condition;
                }
            }

            // get tables
            foreach($this->tables as $table) {
                if( preg_match('/('.DB_TABLE_PREFIX.'t_item$)/', $table, $matches ) ) {
                    // t_item is allways included
                } else if( preg_match('/('.DB_TABLE_PREFIX.'t_item_description( as d)?)/', $table, $matches ) ) {
                    // t_item_description is allways included
                } else if( preg_match('/'.DB_TABLE_PREFIX.'t_category/', $table, $matches ) ) {
                    // t_category is allways included
                } else if( preg_match('/('.DB_TABLE_PREFIX.'t_category_description( as cd)?)/', $table, $matches ) ) {
                    // t_item_description
                    $aData['tables'][] = $matches[1];
                } else if( preg_match('/('.DB_TABLE_PREFIX.'t_item_resource)/', $table, $matches ) ) {
                    $aData['withPicture'] = true;
                } else {
                    $aData['no_catched_tables'][] = $table;
                }
            }

            // get order & limit
            $aData['order_column']      = $this->order_column;
            $aData['order_direction']   = $this->order_direction;
            $aData['limit_init']        = $this->limit_init;
            $aData['results_per_page']  = $this->results_per_page;

            return $aData;
        }

        /**
         * Return json with all search attributes
         *
         * @return string
         */
        public function toJson($convert = false)
        {
            if($convert) {
                $aData = $this->_getConditions();
            } else {
                $aData['price_min']     = $this->price_min / 1000000;
                $aData['price_max']     = $this->price_max / 1000000;
                $aData['aCategories']   = $this->categories;
                // locations
                $aData['city_areas']    = $this->city_areas;
                $aData['cities']        = $this->cities;
                $aData['regions']       = $this->regions;
                $aData['countries']     = $this->countries;
                // pattern
                $aData['withPattern']   = $this->withPattern;
                $aData['sPattern']      = $this->sPattern;
                if($this->withPicture) {
                    $aData['withPicture']   = $this->withPicture;
                }

                if($this->onlyPremium) {
                    $aData['onlyPremium']   = $this->onlyPremium;
                }

                $aData['tables']        = $this->tables;
                $aData['tables_join']   = $this->tables_join;

                $aData['no_catched_tables']     = $this->tables;
                $aData['no_catched_conditions'] = $this->conditions;

                $aData['user_ids']          = $this->user_ids;

                // get order & limit
                $aData['order_column']      = $this->order_column;
                $aData['order_direction']   = $this->order_direction;
                $aData['limit_init']        = $this->limit_init;
                $aData['results_per_page']  = $this->results_per_page;
            }
            return json_encode($aData);
        }

        public function setJsonAlert($aData)
        {
            $this->priceRange($aData['price_min'], $aData['price_max'] );

            $this->categories   = $aData['aCategories'];
            // locations
            $this->city_areas   = $aData['city_areas'];
            $this->cities       = $aData['cities'];
            $this->regions      = $aData['regions'];
            $this->countries    = $aData['countries'];

            $this->user_ids     = $aData['user_ids'];

            $this->tables_join  = $aData['tables_join'];
            $this->tables       = $aData['no_catched_tables'];
            $this->conditions   = $aData['no_catched_conditions'];

            // get order & limit
            $this->order_column     = $aData['order_column'];
            $this->order_direction  = $aData['order_direction'];
            $this->limit_init       = $aData['limit_init'];
            $this->results_per_page = $aData['results_per_page'];

            // pattern
            if(isset($aData['sPattern']) ) {
                $this->addPattern($aData['sPattern']);
            }
            if( isset($aData['withPicture']) ) {
                $this->withPicture(true);
            }
            if( isset($aData['onlyPremium']) ) {
                $this->onlyPremium(true);
            }
        }
    }

    /* file end: ./oc-includes/osclass/model/Search.php */
?>
